﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using Fuyao.Business.Entities.Models;
using System.Data.Common;
using System.Xml.Linq;

namespace Fuyao.Data.SQLServer.DAO
{
    public class DAOParametro
    {
        string strCnx = clsConexionDB.getConexionSQL();

        public bool InsertVendedor(Vendedor BE)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("INSERT INTO dbo.Vendedor (NombreVendedor, ApellidoVendedor, Estado) ");
                strCommand.Append("VALUES (@NombreVendedor,@ApellidoVendedor,1)");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@NombreVendedor", SqlDbType.VarChar, 40).Value = BE.NombreVendedor;
                sqlCommand.Parameters.Add("@ApellidoVendedor", SqlDbType.VarChar, 50).Value = BE.ApellidoVendedor;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public bool UpdateVendedor(Vendedor BE)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("UPDATE dbo.Vendedor set NombreVendedor=@NombreVendedor, ApellidoVendedor=@ApellidoVendedor, Estado=@Estado   ");
                strCommand.Append("WHERE VendedorID = @VendedorID ");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@VendedorID", SqlDbType.Int).Value = BE.VendedorID;
                sqlCommand.Parameters.Add("@NombreVendedor", SqlDbType.VarChar, 40).Value = BE.NombreVendedor;
                sqlCommand.Parameters.Add("@ApellidoVendedor", SqlDbType.VarChar, 50).Value = BE.ApellidoVendedor;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = BE.Estado;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public bool DeleteVendedor(int idVend)
        {
            bool _result = false;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("UPDATE dbo.Vendedor set Estado=@Estado ");
                strCommand.Append("WHERE VendedorID = @VendedorID ");

                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@VendedorID", SqlDbType.Int).Value = idVend;
                sqlCommand.Parameters.Add("@Estado", SqlDbType.Bit).Value = false;
                cnx.Open();
                _result = Convert.ToBoolean(sqlCommand.ExecuteNonQuery());

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return _result;
        }

        public List<Parametro> getParametroList()
        {

            Parametro oParametro = null;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);
            List<Parametro> listParametro = new List<Parametro>();

            try
            {
                strCommand.Append("SELECT ParametroID,TableName,  Nombre, Valor, Notas FROM PARAMETRO");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.Text;

                cnx.Open();
                SqlDataReader dr = sqlCommand.ExecuteReader();

                while (dr.Read())
                {
                    oParametro = new Parametro();
                    if (!Convert.IsDBNull(dr["ParametroID"])) oParametro.ParametroID = Convert.ToInt32(dr["ParametroID"]);
                    if (!Convert.IsDBNull(dr["TableName"])) oParametro.TableName = Convert.ToString(dr["TableName"]);
                    if (!Convert.IsDBNull(dr["Nombre"])) oParametro.Nombre = Convert.ToString(dr["Nombre"]);
                    if (!Convert.IsDBNull(dr["Valor"])) oParametro.Valor = Convert.ToString(dr["Valor"]);
                    if (!Convert.IsDBNull(dr["Notas"])) oParametro.Notas = Convert.ToString(dr["Notas"]);
                    listParametro.Add(oParametro);
                }

                dr.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return listParametro;
        }

        public Parametro getParametroByNombre(string nombre)
        {

            Parametro oParametro = null;
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);

            try
            {
                strCommand.Append("SELECT ParametroID,TableName,  Nombre, Valor, Notas FROM PARAMETRO where Nombre = @Nombre ");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.Parameters.Add("@Nombre", SqlDbType.VarChar).Value = nombre;

                sqlCommand.CommandType = CommandType.Text;

                cnx.Open();
                SqlDataReader dr = sqlCommand.ExecuteReader();

                if (dr.Read())
                {
                    oParametro = new Parametro();
                    if (!Convert.IsDBNull(dr["ParametroID"])) oParametro.ParametroID = Convert.ToInt32(dr["ParametroID"]);
                    if (!Convert.IsDBNull(dr["TableName"])) oParametro.TableName = Convert.ToString(dr["TableName"]);
                    if (!Convert.IsDBNull(dr["Nombre"])) oParametro.Nombre = Convert.ToString(dr["Nombre"]);
                    if (!Convert.IsDBNull(dr["Valor"])) oParametro.Valor = Convert.ToString(dr["Valor"]);
                    if (!Convert.IsDBNull(dr["Notas"])) oParametro.Notas = Convert.ToString(dr["Notas"]);
                }

                dr.Close();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return oParametro;
        }

        public bool InsertParametroList(List<Parametro> listParametros)
        {
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);
            bool result = false;
            try
            {
                strCommand.Append("FYAO_InsertParametroList");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                sqlCommand.Parameters.Add("@XML", SqlDbType.Xml).Value = getItemLinqXML_InsertParametros(listParametros);

                cnx.Open();
                if (sqlCommand.ExecuteNonQuery() > 0)
                    result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return result;
        }


        public bool UpdateParametroList(List<Parametro> listParametros)
        {
            StringBuilder strCommand = new StringBuilder();
            SqlConnection cnx = new SqlConnection(strCnx);
            bool result = false;
            try
            {
                strCommand.Append("FYAO_UpdateParametroList");
                SqlCommand sqlCommand = new SqlCommand(strCommand.ToString(), cnx);
                sqlCommand.CommandType = CommandType.StoredProcedure;

                sqlCommand.Parameters.Add("@XML", SqlDbType.Xml).Value = getItemLinqXML_UpdateParametros(listParametros);

                cnx.Open();
                if (sqlCommand.ExecuteNonQuery() > 0)
                    result = true;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
                cnx.Close();
            }
            return result;
        }

        private string getItemLinqXML_InsertParametros(List<Parametro> listParametros)
        {
            var xEle = new XElement("ITEMS",//raiz
                            from item in listParametros
                            select new XElement("ITEM",//nodo
                                   new XAttribute("TableName", item.TableName ?? string.Empty),
                                   new XAttribute("Nombre", item.Nombre ?? string.Empty),
                                   new XAttribute("Valor", item.Valor ?? string.Empty),
                                   new XAttribute("Notas", item.Notas ?? string.Empty),
                                   new XAttribute("CreadoPor",item.CreadoPor ) //item.CreadoPor
                                   ));

            return xEle.ToString();
        }

        private string getItemLinqXML_UpdateParametros(List<Parametro> listParametros)
        {
            var xEle = new XElement("ITEMS",//raiz
                            from item in listParametros
                            select new XElement("ITEM",//nodo                                    
                                   new XAttribute("ParametroID", item.ParametroID),
                                   new XAttribute("TableName", item.TableName ?? string.Empty),
                                   new XAttribute("Nombre", item.Nombre ?? string.Empty),
                                   new XAttribute("Valor", item.Valor ?? string.Empty),
                                   new XAttribute("Notas", item.Notas ?? string.Empty),
                                   new XAttribute("ModificadoPor", item.CreadoPor)
                                   )); //atributos                                  
            return xEle.ToString();
        }

    }
}
